﻿CREATE FUNCTION innovator.fn_CheckEntityAccess (
    @PermissionID      CHAR(32),      -- 数据权限ID
    @CreaterRoleId    CHAR(32),       -- 固定角色ID (Creator)
    @OwnerRoleId      CHAR(32),       -- 固定角色ID (Owner)
    @EveryoneRoleId   CHAR(32),       -- 固定角色ID (Everyone)
    @CanView          BIT = 1,        -- 是否允许查看
    @CanEdit          BIT = 0,        -- 是否允许编辑
    @CanDelete        BIT = 0,        -- 是否允许删除
    @CurrentUserID    CHAR(32),       -- 当前用户ID
    @UserIdentityList NVARCHAR(MAX),  -- 逗号分隔的身份ID列表
    @created_by_id    CHAR(32),       -- 数据创建人ID
    @owned_by_id      CHAR(32),       -- 数据拥有者ID
    @team_id          CHAR(32)        -- 数据所属团队ID
)
RETURNS BIT
AS
BEGIN
    -- 统一处理空GUID和空字符串为NULL
    SET @PermissionID = NULLIF(NULLIF(@PermissionID, '00000000-0000-0000-0000-000000000000'), '');
    SET @created_by_id = NULLIF(NULLIF(@created_by_id, '00000000-0000-0000-0000-000000000000'), '');
    SET @owned_by_id = NULLIF(NULLIF(@owned_by_id, '00000000-0000-0000-0000-000000000000'), '');
    SET @team_id = NULLIF(NULLIF(@team_id, '00000000-0000-0000-0000-000000000000'), '');

    -- 提前退出：PermissionID为空
    IF @PermissionID IS NULL RETURN 0;

    -- 使用内存优化表变量提升性能
    DECLARE @UserIdentityTable TABLE (ID char(32) PRIMARY KEY WITH (IGNORE_DUP_KEY = ON));

    -- 仅当有身份数据时处理
    IF @UserIdentityList IS NOT NULL AND LEN(@UserIdentityList) > 0
    BEGIN
        INSERT INTO @UserIdentityTable
            (ID)
        SELECT TRY_CAST(value AS char(32))
        FROM STRING_SPLIT(@UserIdentityList, ',')
        WHERE TRY_CAST(value AS char(32)) IS NOT NULL;
    END

    -- 优化检查逻辑：分步独立检查，避免OR条件导致的扫描
    -- 1. 检查Everyone权限
    IF EXISTS (
        SELECT 1
    FROM ACCESS a
    WHERE a.SOURCE_ID = @PermissionID
        AND a.RELATED_ID = @EveryoneRoleId
        AND (a.CAN_GET >= @CanView)
        AND (a.CAN_UPDATE >= @CanEdit)
        AND (a.CAN_DELETE >= @CanDelete)
    ) RETURN 1;

    -- 2. 检查用户身份权限
    IF EXISTS (
        SELECT 1
    FROM ACCESS a
        INNER JOIN @UserIdentityTable uit ON a.RELATED_ID = uit.ID
    WHERE a.SOURCE_ID = @PermissionID
        AND (a.CAN_GET >= @CanView)
        AND (a.CAN_UPDATE >= @CanEdit)
        AND (a.CAN_DELETE >= @CanDelete)
    ) RETURN 1;

    -- 3. 检查团队权限（团队ID非空时）
    IF @team_id IS NOT NULL AND EXISTS (
        SELECT 1
        FROM ACCESS a
        WHERE a.SOURCE_ID = @PermissionID
            AND a.RELATED_ID = @team_id
            AND (a.CAN_GET >= @CanView)
            AND (a.CAN_UPDATE >= @CanEdit)
            AND (a.CAN_DELETE >= @CanDelete)
    ) RETURN 1;

    -- 4. 检查创建者权限（需匹配当前用户）
    IF @created_by_id = @CurrentUserID AND EXISTS (
        SELECT 1
        FROM ACCESS a
        WHERE a.SOURCE_ID = @PermissionID
            AND a.RELATED_ID = @CreaterRoleId
            AND (a.CAN_GET >= @CanView)
            AND (a.CAN_UPDATE >= @CanEdit)
            AND (a.CAN_DELETE >= @CanDelete)
    ) RETURN 1;

    -- 5. 检查拥有者权限（用户身份匹配拥有者）
    IF EXISTS (
        SELECT 1
    FROM ACCESS a
    WHERE a.SOURCE_ID = @PermissionID
        AND a.RELATED_ID = @OwnerRoleId
        AND EXISTS (SELECT 1
        FROM @UserIdentityTable
        WHERE ID = @owned_by_id)
        AND (a.CAN_GET >= @CanView)
        AND (a.CAN_UPDATE >= @CanEdit)
        AND (a.CAN_DELETE >= @CanDelete)
    ) RETURN 1;

    RETURN 0;
END
